<?php
namespace Operationtest\Classe;

use PHPExcel;

class ExcelManager extends \PHPExcel {
    protected $table;
    protected $excel;
    protected $reader = null;
    protected $writer = null;
    protected $filename = '';
    protected $dir = '';
    protected $defaultDir = '';

    public function __construct(Array $table=array()) {
        parent::__construct();
        $this->table = $table;
        $this->excel = new \PHPExcel();
    }
    
    public function load($filename = 'synthese.xls') {
        $pwd = getcwd();
        $dir = '/upload/operationtest/excel/';
        $dirPublic = '/public/upload/operationtest/excel/';
        if(file_exists($pwd.$dir.$filename)) {
	    $this->openExcel($pwd.$dir.$filename);
	} elseif(file_exists($pwd.$dirPublic.$filename)) {
	    $this->openExcel($pwd.$dirPublic.$filename);
	} else {
	    throw new \Exception("Le fichier '$pwd$dir$filename' ou '$pwd$dirPublic$filename' n'existe pas");
	}
    }
    
    public function openExcel($fullNname) {
        $this->reader = \PHPExcel_IOFactory::createReader("Excel5");
        $this->reader->setReadDataOnly(false);
        $this->excel = $this->reader->load($fullNname);
        $this->dir = dirname($fullNname);
        //$this->filename = str_replace('.xls', '1.xls', substr($fullNname, strrpos($fullNname, '/')));
        $this->writer = new \PHPExcel_Writer_Excel5($this->excel);
        $this->defaultDir = $this->dir.$this->filename;
    }
    
    public function fillExcel($data, $ligne=2, $type='month') {
        $sheet = $this->excel->getActiveSheet();
        
        // Style par défaut
        $this->excel->getDefaultStyle()->applyFromArray(array(
            'font' => array(
                'name' => 'Arial',
                'size' => 10,
            ),
            'alignment' => array(
                'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_RIGHT
            ),
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array(
                        'rgb' => '000000'
                    )
                )
            )
        ));
        
        $sheet->duplicateStyleArray(array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array(
                        'rgb' => '000000'
                    )
                )
            ),
            'fill' => array(
                'type' => \PHPExcel_Style_Fill::FILL_SOLID,
                'startcolor' => array(
                    'rgb' => '4182CD'
                )        
            )
        ),'B1:AZ1');
        
        // Style pour la première colonne
        $sheet->getStyle("A$ligne")->applyFromArray(array(
            'font' => array(
                'bold' => true,
                'color' => array(
                    'rgb' => 'FFFFFF'
                )
            ),
            'alignment' => array(
                'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_RIGHT
            ),
            'borders' => array(
                'allborders' => array(
                    'color' => array(
                        'rgb' => '4A7EBB'
                    )
                )
            )
        ));
        
        // Style du mois, des semaines et des jours
        if($type == 'month') {
            $sheet->getStyle("A$ligne")->getFill()->applyFromArray(array(
                'type' => \PHPExcel_Style_Fill::FILL_SOLID,
                'startcolor' => array(
                    'rgb' => '4182CD'
                )        
            ));
            $sheet->getStyle("A$ligne")->applyFromArray(array(
                'font' => array(
                    'bold' => true
                ),
                'alignment' => array(
                    'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER
                )
            )); 
        } elseif($type == 'week') {
            $sheet->getStyle("A$ligne")->getFill()->applyFromArray(array(
                'type' => \PHPExcel_Style_Fill::FILL_SOLID,
                'startcolor' => array(
                    'rgb' => '77933C'
                )
            ));
            $sheet->getStyle("A$ligne")->applyFromArray(array(
                'font' => array(
                    'bold' => true
                ),
                'alignment' => array(
                    'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER
                )
            ));                    
        } elseif($type == 'day') {
            $sheet->getStyle("A$ligne")->getFill()->applyFromArray(array(
                'type' => \PHPExcel_Style_Fill::FILL_SOLID,
                'startcolor' => array(
                    'rgb' => 'FFFF99'
                )
            ));
            $sheet->getStyle("A$ligne")->applyFromArray(array(
                'font' => array(
                    'bold' => false,
                    'color' => array(
                        'rgb' => '000000'
                    )
                ),
                'alignment' => array(
                    'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT
                )
            ));   
        }
        
        $sheet->setCellValue("A$ligne", $data[0]);
        
	$sheet->setCellValue("B$ligne", $data[1]);
	$sheet->setCellValue("C$ligne", $data[2]);
        
	$sheet->setCellValue("D$ligne", $data[3]);
	$sheet->setCellValue("E$ligne", $data[4]);
	$sheet->setCellValue("F$ligne", $data[5]);
        
	$sheet->setCellValue("G$ligne", $data[6]);
	$sheet->setCellValue("H$ligne", $data[7]);
	$sheet->setCellValue("I$ligne", $data[8]);
	$sheet->setCellValue("J$ligne", $data[9]);
	$sheet->setCellValue("K$ligne", $data[10]);
	$sheet->setCellValue("L$ligne", $data[11]);
	$sheet->setCellValue("M$ligne", $data[12]);
	$sheet->setCellValue("N$ligne", $data[13]);
	$sheet->setCellValue("O$ligne", $data[14]);
        
	$sheet->setCellValue("P$ligne", $data[15]);
        
	$sheet->setCellValue("Q$ligne", $data[16]);
	$sheet->setCellValue("R$ligne", $data[17]); 
        
	$sheet->setCellValue("S$ligne", $data[18]);
	$sheet->setCellValue("T$ligne", $data[19]);        
	$sheet->setCellValue("U$ligne", $data[20]);
	$sheet->setCellValue("V$ligne", $data[21]);
	$sheet->setCellValue("W$ligne", $data[22]);
	$sheet->setCellValue("X$ligne", $data[23]);
	$sheet->setCellValue("Y$ligne", $data[24]);
	$sheet->setCellValue("Z$ligne", $data[25]);
        
	$sheet->setCellValue("AA$ligne", $data[26]);
	$sheet->setCellValue("AB$ligne", $data[27]);
        
	$sheet->setCellValue("AC$ligne", $data[28]);
	$sheet->setCellValue("AD$ligne", $data[29]);        
	$sheet->setCellValue("AE$ligne", $data[30]);
	$sheet->setCellValue("AF$ligne", $data[31]);
        
	$sheet->setCellValue("AG$ligne", $data[32]);
	$sheet->setCellValue("AH$ligne", $data[33]);        
	$sheet->setCellValue("AI$ligne", $data[34]);
	$sheet->setCellValue("AJ$ligne", $data[35]);
	$sheet->setCellValue("AK$ligne", $data[36]);
	$sheet->setCellValue("AL$ligne", $data[37]);
        
	$sheet->setCellValue("AM$ligne", $data[38]);
	$sheet->setCellValue("AN$ligne", $data[39]);        
	$sheet->setCellValue("AO$ligne", $data[40]);
	$sheet->setCellValue("AP$ligne", $data[41]);
	$sheet->setCellValue("AQ$ligne", $data[42]);
	$sheet->setCellValue("AR$ligne", $data[43]);
        
	$sheet->setCellValue("AS$ligne", $data[44]);
        $sheet->setCellValue("AT$ligne", $data[45]);        
	$sheet->setCellValue("AU$ligne", $data[46]);
	$sheet->setCellValue("AV$ligne", $data[47]);
	$sheet->setCellValue("AW$ligne", $data[48]);
	$sheet->setCellValue("AX$ligne", $data[49]);
        
	$sheet->setCellValue("AY$ligne", $data[50]);
	$sheet->setCellValue("AZ$ligne", $data[51]);
    }
    
    public function save($filename=null) {
        $fullPath = '';
        if(is_null($filename)) {
            $fullPath = $this->dir.'/'.$this->filename;
        } else {
            $fullPath = $this->dir.'/'.$filename;
        }
        $this->writer->save($fullPath);
    }
    
    public function loadBudgetFile($filename) {
        $pwd = getcwd();
        $dir = '/upload/operationtest/excel/';
        $dirPublic = '/public/upload/operationtest/excel/';
        if(file_exists($pwd.$dir.$filename)) {
            return \PHPExcel_IOFactory::load($pwd.$dir.$filename);
	} elseif(file_exists($pwd.$dirPublic.$filename)) {
	    return \PHPExcel_IOFactory::load($pwd.$dirPublic.$filename);
	} else {
	    throw new \Exception("Le fichier '$pwd$dir$filename' ou '$pwd$dirPublic$filename' n'existe pas");
	}
    }
}
